﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
using System.Data;
using Task_Network_Center.General;
using System.Windows;

namespace Task_Network_Center.DataBaseTable
{
    class UpdateTable
    {
        MySqlConnection conn = null;
        MySqlConnection conn2 = null;

        public int GetNumberOfLastTask(int userID)
        {
          
            conn = GCGGeneralLib.DataBaseConnection.DbConnection();
            MySqlCommand cmd = new MySqlCommand("SELECT last_count FROM tableupdate WHERE user = " + userID, conn);
           
            try
            {
                conn.Open();
                MySqlDataReader dr = cmd.ExecuteReader();
                dr.Read();
                int result = dr.GetInt32(0);
                conn.Close();
                return result;
            }
            catch (Exception e)
            {
                MessageBox.Show("Erreur UpdateTable - GetNumberOfLastTask: " + e);
                return -1;
            }
        }


        public int GetNumberOfActualTask(int userID)
        {
            try
            {
                conn = GCGGeneralLib.DataBaseConnection.DbConnection();

                String query = "SELECT COUNT(*) FROM tablelistdestaches_general WHERE executant = " + userID;
                MySqlCommand cmd = new MySqlCommand(query, conn);
                conn.Open();

                Int64 result = (Int64)cmd.ExecuteScalar();

                conn.Close();
                conn = null;

                return Convert.ToInt32(result);
            }
            catch (Exception e)
            {
                MessageBox.Show("Erreur UpdateTable - GetNumberOfActualTask: " + e);
                return -1;
            }
        }

        public bool UpdateLastCountCell(int userID,int newCount)
        {
            conn = GCGGeneralLib.DataBaseConnection.DbConnection();
            MySqlCommand cmd = new MySqlCommand("UPDATE tableupdate SET last_count = " + newCount + " WHERE user = " + userID, conn);
            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();
            return true;
        }



        public bool InsertRow(int user, int last_count)
        {
            String query = "INSERT INTO tableupdate (idtableupdate,user,last_count) VALUE (@p1,@p2,@p3)";

            int id = updateID();

            conn = GCGGeneralLib.DataBaseConnection.DbConnection();
            MySqlCommand cmd = new MySqlCommand(query, conn);
            try
            {
                conn.Open();
                cmd.Parameters.AddWithValue("p1", id);
                cmd.Parameters.AddWithValue("p2", user);
                cmd.Parameters.AddWithValue("p3", last_count);



                cmd.ExecuteNonQuery();
                conn.Close();
                return true;
            }
            catch (Exception e)
            {
                MessageBox.Show("Erreur UpdateTable - InsertRow: " + e);
                return false;
            }

        }



        public bool UpdateRow(int id, int user, int last_count)
        {
            String query = "UPDATE tableupdate SET user = @p1, last_count = @p2 WHERE idtableupdate = @p3";
            conn = GCGGeneralLib.DataBaseConnection.DbConnection();
            MySqlCommand cmd = new MySqlCommand(query, conn);
            try
            {
                conn.Open();
                cmd.Parameters.AddWithValue("p3", id);
                cmd.Parameters.AddWithValue("p1", user);
                cmd.Parameters.AddWithValue("p2", last_count);

                cmd.ExecuteNonQuery();
                conn.Close();
                return true;

            }
            catch (Exception e)
            {

                MessageBox.Show("Erreur UpdateTable - UpdateRow: " + e);
                return false;
            }

        }


        private int updateID()
        {

            conn2 = GCGGeneralLib.DataBaseConnection.DbConnection();
            String query = "SELECT LAST_INSERT_ID() FROM tableupdate";
            MySqlCommand cmd = conn2.CreateCommand();
            cmd.CommandText = query;
            try
            {

                conn2.Open();
                cmd.ExecuteScalar();
                int count = Convert.ToInt32(cmd.LastInsertedId);
                conn2.Close();
                conn2 = null;
                return count + 1;
            }
            catch (Exception e)
            {
                MessageBox.Show("Erreur UpdateTable - updateID: " + e);
                return -1;
            }

        }

    }
}
